'''
Created on Oct 23, 2009
@author: CommisGordon
'''

import sql,random

'''
 All of the important opcode variables
'''
DATA = 0
LOGIN_USERNAME = 1
LOGIN_PASSWORD = 12
CHAT = 3
MOVE_FORWARD = 4
S_MOVE_FORWARD = 5
MOVE_BACKWARD = 6
S_MOVE_BACKWARD = 7
MOVE_LEFT = 8
S_MOVE_LEFT = 9
MOVE_RIGHT = 10
S_MOVE_RIGHT = 11
ROTATE_LEFT = 2
S_ROTATE_LEFT = 13
ROTATE_RIGHT = 14
S_ROTATE_RIGHT = 15
LOGIN_SUCCESSFUL = 16
LOGIN_FAILED = 17
SHOOT_BULLET = 18

'''
 NetworkHandler class
 Takes care of communication with the server
 NOTE: Only for use with online play
'''
class NetworkHandler:
    def __init__(self,location="localhost",port=9999):
        import socket
        self.sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        self.sock.connect((location,port))
        
    def write(self, value):
        self.sock.send(chr(value.opcode))
        if value.length != None:
            self.sock.send(chr(value.length))
            self.sock.send(value.data)
    def read(self):
        opcode = ord(self.sock.recv(1))
        pid = ord(self.sock.recv(1))
        data = None
        if opcode < 4:
            length = self.sock.recv(1)
            data = self.sock.recv(length)
        if data != None:
            retval = Packet(opcode,data,pid)
        else:
            retval = Packet(opcode,None,pid)
        return retval

'''
 Packet Class
 Represents data structure of packets transferred between client and server
 NOTE: ONly to be used with online play
'''        
class Packet:
    def __init__(self, opcode, data = None, pid=None):
        self.opcode = opcode
        self.pid = pid
        self.length = None
        self.data = None
        if data != None:
            self.data = data
            self.length = len(data)
    
    def getOpcode(self):
        return self.opcode
    def getData(self):
        return self.data
    def getLength(self):
        return self.length
    def getPid(self):
        return self.pid
    def hasData(self):
        retval = None
        if self.data != None:
            retval = True
        else:
            retval = False
        return retval

'''
 NetworkGameHandler class
 Does all of the heavy lifting for SQL communication for a network game
'''
class NetworkGameHandler:
    '''
     __init__(String,String,Player)
     Constructor, connects to the postgresql database and inserts player into all necessary databases
    '''
    def __init__(self,location,username,password,player):
        self.player = player
        self.pid = self.player.pid
        self.sql = sql.PostgreSQL("pewpew",location,username,password)
        self.sql.query("BEGIN; INSERT INTO online_player_controls (pid) VALUES ("+str(self.pid)+");")
        self.sql.query("INSERT INTO chardata (pid,username,x,y,rot,lastupdate) VALUES("+str(self.pid)+",'"+player.username+"',0,0,0,localtime);")
        self.sql.query("INSERT INTO leaderboard (pid,kills,deaths) VALUES ("+str(self.pid)+",0,0); COMMIT;")
        last_chat = self.sql.queryd("SELECT localtime;")
        self.last_chat = last_chat[0]['time'];
        
    '''
     getPlayers()
     Gets all of the players out of the database
     @return: list of dictionaries containing players data
    '''
    def getPlayers(self):
        players = self.sql.queryd("SELECT * FROM online_player_controls WHERE pid<>"+str(self.pid)+";")
        return players
    
    '''
     getBullets()
     Gets all of the bullets from the database
     @return: list of dictionaries containing bullet data
    '''
    def getBullets(self):
        bullets = self.sql.queryd("SELECT bid,pid,x,y,rot,(EXTRACT(minute from (localtime-timeshot))*60 + EXTRACT(second from (localtime-timeshot))) as timeshot FROM bullet;")
        return bullets
    
    '''
     getPlayerData()
     Gets all of the locational and health data about all players in the database
     @return: list of dictionaries containing player data
    '''
    def getPlayerData(self):
        players = self.sql.queryd("SELECT username,health,shield,pid,x,y,rot,vx,vy,(EXTRACT(minute from (localtime - lastupdate))*60*1000 + EXTRACT(second from (localtime - lastupdate))*1000) as lastupdate FROM chardata WHERE pid<>"+str(self.pid)+";")
        removals = []
        retp = []
        for p in players:
            if p['lastupdate']>30000 or p['lastupdate']<0:
                    removals.append(p['pid'])
                    print "REMOVING - "+str(p['pid'])
                    self.sql.query("BEGIN; DELETE FROM online_player_controls WHERE pid="+str(p['pid'])+";")
                    self.sql.query("DELETE FROM chardata WHERE pid="+str(p['pid'])+";")
                    self.sql.query("DELETE FROM leaderboard WHERE pid="+str(p['pid'])+"; COMMIT;")
        for p in players:
            if p not in removals:
                retp.append(p)
        return (retp,removals)
    
    '''
     setControls()
     Saves player's current control state to the database
    '''
    def setControls(self,conts):
        query = "BEGIN; UPDATE online_player_controls SET "
        doquery = False
        if MOVE_FORWARD in conts and S_MOVE_FORWARD not in conts:
            query+="\"MOVE_FORWARD\"=TRUE, \"S_MOVE_FORWARD\"=FALSE, "
            doquery =True
        elif S_MOVE_FORWARD in conts and MOVE_FORWARD not in conts:
            query+="\"S_MOVE_FORWARD\"=TRUE, \"MOVE_FORWARD\"=FALSE, "
            doquery =True
        if MOVE_BACKWARD in conts and S_MOVE_BACKWARD not in conts:
            query+="\"MOVE_BACKWARD\"=TRUE, \"S_MOVE_BACKWARD\"=FALSE, "
            doquery =True
        elif S_MOVE_BACKWARD in conts and MOVE_BACKWARD not in conts:
            query+="\"S_MOVE_BACKWARD\"=TRUE, \"MOVE_BACKWARD\"=FALSE, "
            doquery =True
        if ROTATE_LEFT in conts and S_ROTATE_LEFT not in conts:
            query+="\"ROTATE_LEFT\"=TRUE, \"S_ROTATE_LEFT\"=FALSE, "
            doquery =True
        elif S_ROTATE_LEFT in conts and ROTATE_LEFT not in conts:
            query+="\"S_ROTATE_LEFT\"=TRUE, \"ROTATE_LEFT\"=FALSE, "
            doquery =True
        if ROTATE_RIGHT in conts and S_ROTATE_RIGHT not in conts:
            query+="\"ROTATE_RIGHT\"=TRUE, \"S_ROTATE_RIGHT\"=FALSE, "
            doquery =True
        elif S_ROTATE_RIGHT in conts and ROTATE_RIGHT not in conts:
            query+="\"S_ROTATE_RIGHT\"=TRUE, \"ROTATE_RIGHT\"=FALSE, "
            doquery =True
                
        if(doquery):
            query = query.rstrip(", ")
            query+= " "+"WHERE pid="+str(self.pid)
            self.sql.query(query+"; COMMIT;")
        
    '''
     savePosition(Tuple)
     Saves player's current locational and health data to the server
     @param position: Tuple containing the (x,y), rotation, velocity, health and shield of the local player
    '''            
    def savePosition(self,position):
        query = "BEGIN; UPDATE chardata SET x ="+str(position[0])+" , y="+str(position[1])+" , rot = "+str(position[4])+" ,  vx="+str(position[2])+" , vy="+str(position[3])+", lastupdate=localtime, health="+str(self.player.health)+", shield="+str(self.player.shield)+" where pid="+str(self.pid)+"; COMMIT;"
        self.sql.query(query)
    
    '''
     deleteBullet(Numeric)
     removes a bullet from the database
     @param bid: bulletId of the bullet to be deleted
    '''  
    def deleteBullet(self,bid):
        self.sql.query("BEGIN; DELETE FROM bullet WHERE bid='"+str(bid)+"'; COMMIT;") 
    
    '''
     fireBullet(Numeric,Numeric,Numeric,Numeric,Numeric)
     Inserts a new bullet into the database
     @param bid: id of the bullet to insert
     @param pid: player id of the player who shot the bullet
     @param xpos: x position the shot was fired at
     @param ypos: y position the shot was fired at
     @param rot: rotation the shot was fired at
    '''         
    def fireBullet(self,bid,pid,xpos,ypos,rot):
        self.sql.query("BEGIN; INSERT INTO bullet (bid,pid,x,y,rot,timeshot) VALUES("+str(bid)+","+str(pid)+","+str(xpos)+","+str(ypos)+","+str(rot)+",localtime); COMMIT;")
    
    '''
     appendChatMessage(String)
     Inserts a new chat message into the database
     @param msg: message to append
    '''     
    def appendChatMessage(self,msg):
        self.sql.query("BEGIN; INSERT INTO chat (mid,pid,msg_time,message) VALUES ("+str(random.randint(0,10000))+","+str(self.player.pid)+",localtime,'"+str(msg)+"'); COMMIT;")
    
    '''
     getChatMessages()
     Get all of the new chat messages from the server
     @return: List of new chat messages
    '''    
    def getChatMessages(self):
        msgs = self.sql.queryd("SELECT localtime,chat.message,chardata.username FROM chat,chardata WHERE (chat.pid = chardata.pid) AND (msg_time - time '"+self.last_chat+"')>time '00:00:00.000';")
        amsgs = []
        for m in msgs:
            amsgs.append("["+m['username']+"] "+m['message'])
            self.last_chat = m['time']
        return amsgs
    
    '''
     addKill(Numeric)
     Adds a kill to the player who killed localplayer, and adds a death to localplayer
     @param pid: id of the player who killed localplayer
    ''' 
    def addKill(self,pid):
        self.sql.query("BEGIN; UPDATE leaderboard SET kills = kills+1 WHERE pid="+str(pid)+";")
        self.sql.query("UPDATE leaderboard SET deaths = deaths+1 WHERE pid="+str(self.pid)+"; COMMIT;")
    
    '''
     getLeaderboard()
     retrieves leaderboard information from the database
     @return: list of dictionaries containing leaderboard information
    '''
    def getLeaderboard(self):
        return self.sql.queryd("SELECT chardata.username as player,leaderboard.kills,leaderboard.deaths FROM chardata,leaderboard WHERE (chardata.pid = leaderboard.pid);")